********************************************************************************
*                                                                              *
*                               CREATE ABR DATA                                *
*                                                                              *
********************************************************************************

cd "H:\"


import spss be_id reg_datum_ontstaan reg_datum_opheffing datum_ontstaan datum_opheffing using "G:\Bedrijven\ABR\1967-200606\080418 ABR-Backbone 1967-200606V1 be_id.sav", clear
rename be_id beid
g beidstyear = floor(reg_datum_ontstaan/100)
keep beid beidstyear
save "Robots and Workers\Data\ABR_temp.dta", replace


qui forvalues year = 2000(1)2020 {
	nois _dots `year' 0	
	if `year' == 2000 {
		import spss using "G:\Bedrijven\ABR\2000\080213 ABR-Bedrijfseenheden 2000V1.SAV", case(lower) clear
	}
	if `year' == 2001 {
		import spss using "G:\Bedrijven\ABR\2001\080214 ABR-Bedrijfseenheden 2001V1.SAV", case(lower) clear
	}
	if `year' == 2002 {
		import spss using "G:\Bedrijven\ABR\2002\080201 ABR-Bedrijfseenheden 2002V1.SAV", case(lower) clear
	}
	if `year' == 2003 {
		import spss using "G:\Bedrijven\ABR\2003\080130 ABR-Bedrijfseenheden 2003V1.SAV", case(lower) clear
	}
	if `year' == 2004 {
		import spss using "G:\Bedrijven\ABR\2004\080110 ABR-Bedrijfseenheden 2004V1.SAV", case(lower) clear
	}
	if `year' == 2005 {
		import spss using "G:\Bedrijven\ABR\2005\071005 ABR-Bedrijfseenheden 2005V1.SAV", case(lower) clear
	}	
	if `year' == 2006 {
		import spss using "G:\Bedrijven\ABR\2006\BE_ABR2006V4.SAV", case(lower) clear
	}	
	if `year' == 2007 {
		import spss using "G:\Bedrijven\ABR\2007\BE_ABR2007V4.SAV", case(lower) clear
	}
	if `year' == 2008 {
		import spss using "G:\Bedrijven\ABR\2008\BE_ABR2008V3.SAV", case(lower) clear
	}
	if `year'== 2009 {	
		import spss using "G:\Bedrijven\ABR\2009\BE_ABR2009V4.SAV", case(lower) clear
	}	
	if `year'== 2010 {	
		import spss using "G:\Bedrijven\ABR\2010\BE_ABR2010V4.SAV", case(lower) clear
	}
	if `year'== 2011 {	
		import spss using "G:\Bedrijven\ABR\2011\BE_ABR2011V2.SAV", case(lower) clear
	}
	if `year'== 2012 {	
		import spss using "G:\Bedrijven\ABR\2012\BE_ABR2012V3.SAV", case(lower) clear
	}
	if `year'== 2013 {	
		import spss using "G:\Bedrijven\ABR\2013\BE_ABR2013V3.SAV", case(lower) clear
	}
	if `year' == 2014 {
		import spss using "G:\Bedrijven\ABR\\`year'\BE_ABR`year'V3.SAV", case(lower) clear
	}
	if `year' == 2015 | `year' == 2016 | `year' == 2017 | `year' == 2018 | `year' == 2019 | `year' == 2020 {
		import spss using "G:\Bedrijven\ABR\\`year'\BE_ABR`year'V2.SAV", case(lower) clear
	}
	
	if `year' == 2000 | `year' == 2001 | `year' == 2002 | `year' == 2003 | `year' == 2004 | `year' == 2005  {
		keep be_id ond_id gemcod sbi reg_datum_ontstaan
		rename (be_id ond_id gemcod sbi reg_datum_ontstaan) (beid mfirmid mun sbi93_abr beidstyear)
		replace beidstyear = floor(beidstyear/100)
	}
	if `year' == 2006 | `year' == 2007 | `year' == 2008  {
		keep rbe_identificatie rog_identificatie postcode gemcode rbe_sbi93gecoordineerd rbe_datumontstaantoepassing
		rename (rbe_identificatie rog_identificatie postcode gemcode rbe_sbi93gecoordineerd rbe_datumontstaantoepassing) (beid mfirmid pc6 mun sbi93_abr beidstyear)
		replace beidstyear = floor(beidstyear/10000)
	}	
	if `year' == 2009 {
		keep be_id rog_identificatie postcode gemcode rbe_sbi93gecoordineerd rbe_datumontstaantoepassing
		rename (be_id rog_identificatie postcode gemcode rbe_sbi93gecoordineerd rbe_datumontstaantoepassing) (beid mfirmid pc6 mun sbi93_abr beidstyear)
		replace beidstyear = floor(beidstyear/10000)
	}
	if `year' == 2010 | `year' == 2011 | `year'== 2012 | `year'==2015 | `year'==2016 | `year'==2017  | `year'==2018  | `year'==2019 | `year'== 2020 {
		keep be_id rog_identificatie postcode gemcode rbe_sbi93gecoordineerd rbe_sbigecoordineerd rbe_datumontstaantoepassing
		rename (be_id rog_identificatie postcode gemcode rbe_sbi93gecoordineerd rbe_sbigecoordineerd rbe_datumontstaantoepassing) (beid mfirmid pc6 mun sbi93_abr sbi_abr beidstyear)
		replace beidstyear = floor(beidstyear/10000)
	}
	if `year' == 2013 | `year' == 2014  {
		keep be_id rog_identificatie postcode gemcode rbe_sbigecoordineerd rbe_datumontstaantoepassing
		rename (be_id rog_identificatie postcode gemcode rbe_sbigecoordineerd rbe_datumontstaantoepassing) (beid mfirmid pc6 mun sbi_abr beidstyear)
		replace beidstyear = floor(beidstyear/10000)
	}	
	g year = `year'
	capture destring mun, force replace
	merge m:1 beid using "Robots and Workers\Data\ABR_temp.dta", nogen keep(1 3 4 5) update replace
	do "Robots and Workers\Do-files\Variable labels"
	compress
	save "Robots and Workers\Data\ABR_`year'.dta", replace
}	

erase "Robots and Workers\Data\ABR_temp.dta"

clear all
forvalues year = 2000(1)2020 {
	append using "Robots and Workers\Data\ABR_`year'.dta"
	erase "Robots and Workers\Data\ABR_`year'.dta"
}

replace sbi93_abr = "" if sbi93_abr== "0"

gsort beid -year
replace sbi_abr = sbi_abr[_n-1] if beid==beid[_n-1] & sbi_abr[_n-1]!=""
gsort beid year
replace sbi93_abr = sbi93_abr[_n-1] if beid==beid[_n-1] & sbi93_abr[_n-1]!="" 

compress
save "Robots and Workers\Data\ABR.dta", replace

// Create cross-walk between SBI'93 and SBI'08
drop if sbi93_abr =="" | sbi_abr == ""
bysort sbi93_abr: egen sbi_abr_adj = mode(sbi_abr), minmode
duplicates drop sbi93_abr, force
keep sbi93_abr sbi_abr_adj
rename sbi_abr_adj sbi_abr
g sbi93 = sbi93_abr
g sbi = sbi_abr
save "Robots and Workers\Data\ABR_SBIcrosswalk.dta", replace

// Fill in missing SBI'08 codes
use "Robots and Workers\Data\ABR.dta", clear
merge m:1 sbi93_abr using "Robots and Workers\Data\ABR_SBIcrosswalk.dta", nogen keep( 1 3 4 5) update keepusing(sbi_abr)
drop if sbi_abr == ""

// Remove duplicates
sort beid year beidstyear mfirmid
collapse (min) beidstyear (firstnm) mfirmid sbi93_abr mun pc6 sbi_abr, by(beid year)
*rename (sbi93_abr sbi_abr) (sbi93 sbi)
do "Robots and Workers\Do-files\Variable labels"
save "Robots and Workers\Data\ABR.dta", replace
